package com.nccsjz.back.leaveword.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.nccsjz.base.BaseDAO;
import com.nccsjz.pojo.Leaveword;
import com.nccsjz.utils.Pager;

public class LeavewordDAO extends BaseDAO {
	boolean flag = false;

	public LeavewordDAO(Connection con) {
		super(con);
	}

	// add leaveword
	public boolean addLeaveword(Leaveword leaveword) throws SQLException {
		try {
			String sql = "insert into leaveword (leavewordcontent,leavewordtime,leavewordstate) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, leaveword.getLeavewordcontent());
			Date date = new Date();
			pstmt.setTimestamp(2, new Timestamp(date.getTime()));
			pstmt.setString(3, "0");
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// delete leaveword
	public boolean deleteLeaveword(Long id) throws SQLException {
		try {
			String sql = "delete from leaveword where leavewordid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}

		return flag;
	}

	// get one leaveword
	public Leaveword getLeaveword(Long id) throws SQLException {
		Leaveword lv = null;
		try {
			String sql = "select * from leaveword where leavewordid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				lv = new Leaveword();
				lv.setLeavewordid(rs.getLong("leavewordid"));
				lv.setLeavewordcontent(rs.getString("leavewordcontent"));
				lv.setLeavewordstate(rs.getString("leavewordstate"));
				lv.setLeavewordtime(new Date(rs.getTimestamp("leavewordtime")
						.getTime()));
				lv.setLeavewordreply(rs.getString("leavewordreply"));
				if (null != rs.getTimestamp("replytime")) {
					lv.setReplytime(new Date((rs.getTimestamp("replytime"))
							.getTime()));

				}

			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return lv;
	}

	/**
	 * 
	 * @param pageSize
	 * @param pageNo
	 * @param cas
	 *       cas=0，未审核，     cas=1 前台显示已经审核通过的留言,不审核通过的不显示。cas=2,已回复。cas=3或其他,为全部留言
	 * @return
	 * @throws SQLException
	 */
	public Pager getLvs(int pageSize, int pageNo, int cas) throws SQLException {
		List<Leaveword> list = new ArrayList<Leaveword>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from leaveword ";
			//String sql2 = "select * from(select u.*,rownum rn from(select * from leaveword  order by leavewordtime desc) u where rownum<=?)where rn>?";
			String sql2 = "select * from (select leaveword.*, row_number() over(order by leavewordid desc) rn from leaveword ) u where u.rn > ? and u.rn <= ?";
			
			// 未审核。
			if (cas == 0) {
				sql1 = "select count(*) from leaveword where leavewordstate='0'";
				//sql2 = "select * from(select u.*,rownum rn from(select * from leaveword where leavewordstate='0' order by leavewordtime desc) u where rownum<=?)where rn>?";
				sql2 = "select * from (select leaveword.*, row_number() over(order by leavewordid desc) rn from leaveword where leavewordstate='0') u where u.rn > ? and u.rn <= ?";
			}
			
			// 前台显示已经审核通过的留言,不审核通过的不显示。
			if (cas == 1) {
				sql1 = "select count(*) from leaveword where leavewordstate='1'";
				//sql2 = "select * from(select u.*,rownum rn from(select * from leaveword where leavewordstate='1' order by leavewordtime desc) u where rownum<=?)where rn>?";
				sql2 = "select * from (select leaveword.*, row_number() over(order by leavewordid desc) rn from leaveword where leavewordstate='1') u where u.rn > ? and u.rn <= ?";
			}
			
			// 前台显示已经审核通过的留言,不审核通过的不显示。
			if (cas == 2) {
				sql1 = "select count(*) from leaveword where length(LEAVEWORDREPLY)>0 or LEAVEWORDREPLY is not null ";
				//sql2 = "select * from(select u.*,rownum rn from(select * from leaveword where length(LEAVEWORDREPLY)>0 or LEAVEWORDREPLY is not null order by leavewordtime desc) u where rownum<=?)where rn>?";
				sql2 = "select * from (select leaveword.*, row_number() over(order by leavewordid desc) rn from leaveword where length(LEAVEWORDREPLY)>0 or LEAVEWORDREPLY is not null) u where u.rn > ? and u.rn <= ?";
			}

			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Leaveword lv = new Leaveword();
				lv.setLeavewordid(rs.getLong("leavewordid"));
				lv.setLeavewordcontent(rs.getString("leavewordcontent"));
				lv.setLeavewordstate(rs.getString("leavewordstate"));
				//已恢复
				if(cas==2){
					lv.setLeavewordstate("2");
				}
				lv.setLeavewordtime(new Date(rs.getTimestamp("leavewordtime")
						.getTime()));
				lv.setLeavewordreply(rs.getString("leavewordreply"));
				if (null != rs.getTimestamp("replytime")) {
					lv.setReplytime(new Date((rs.getTimestamp("replytime"))
							.getTime()));
				}
				list.add(lv);

			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	public boolean saveLv(Leaveword lv) throws SQLException {
		boolean f = false;
		String sql = "update leaveword set leavewordreply=? ,replytime=?,leavewordstate=? where leavewordid=?";
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1, lv.getLeavewordreply());
		pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));// 默认为系统时间
		pstmt.setString(3, lv.getLeavewordstate());
		pstmt.setLong(4, lv.getLeavewordid());
		int n = pstmt.executeUpdate();
		if (n > 0) {
			f = true;
		}
		return f;
	}

	public boolean delAll(long[] ids) throws SQLException {
		boolean f = false;
		int[] n = null;
		String sql = "delete leaveword where leavewordid=?";
		this.pstmt = con.prepareStatement(sql);
		if (null != ids) {
			for (int i = 0; i < ids.length; i++) {
				pstmt.setLong(1, ids[i]);
				pstmt.addBatch();
			}
		}
		n = pstmt.executeBatch();
		if (null != n) {
			f = true;
		}
		return f;
	}

}
